﻿/***********************************************************************************************************************************
 *    Name: Trace_Rollup.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Roll up performance metrics into 1-minute intervals.
**/
USE XEvents;
SELECT  ServerName,
        IntervalStart = DATETIME2FROMPARTS(DATEPART(YEAR, StartTime), DATEPART(MONTH, StartTime), DATEPART(DAY, StartTime), DATEPART(HOUR, StartTime), DATEPART(MINUTE, StartTime), 0, 0, 0),
        [Duration (ms)] = SUM(Duration) * 0.001,
        Reads = SUM(Reads),
		Writes = SUM(Writes),
        CPU = SUM(CPU)
FROM    dbo.trace_performance
GROUP BY ServerName,
        DATEPART(YEAR, StartTime),
        DATEPART(MONTH, StartTime),
        DATEPART(DAY, StartTime),
        DATEPART(HOUR, StartTime),
        DATEPART(MINUTE, StartTime)
ORDER BY ServerName, IntervalStart;
GO